﻿Option Strict Off

Imports System.Linq
Imports DocumentFormat.OpenXml
Imports DocumentFormat.OpenXml.Packaging
Imports DocumentFormat.OpenXml.Spreadsheet
Imports System.Text.RegularExpressions
Imports System.ServiceModel.Syndication
Imports System.Xml

Public Class PDC09ListMaker

#Region "Event Handlers"
  Private Sub BuildListButton_Click(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles BuildListButton.Click
    EnableUI(False)

    Dim html As String = ""
    Dim ls = BuildListOpenXML(IO.Path.Combine(My.Application.Info.DirectoryPath, CatalogTextBox.Text))
    html = BuildHTML(ls)

    WebBrowser1.DocumentText = html
    CurrentSessionLabel.Text = ""

    EnableUI(True)

  End Sub

  Private Sub RSSListButton_Click(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles RSSListButton.Click
    EnableUI(False)

    Dim enclosureType As String = "WMV"
    If RSSWMVHighRadioButton.Checked Then
      enclosureType = "WMVHigh"
    ElseIf RSSWMVRadioButton.Checked Then
      enclosureType = "WMV"
    ElseIf RSSMP4RadioButton.Checked Then
      enclosureType = "MP4"
    Else
      enclosureType = "WMV"
    End If

    BuildRSSFiles(IO.Path.Combine(My.Application.Info.DirectoryPath, CatalogTextBox.Text), SaveRSSFilesPathTextbox.Text, RSSSaveFileNameTextBox.Text, enclosureType)

    EnableUI(True)

  End Sub
  Private Sub CopyLinkLabel_LinkClicked(ByVal sender As System.Object, ByVal e As System.Windows.Forms.LinkLabelLinkClickedEventArgs) Handles CopyLinkLabel.LinkClicked

    If WebBrowser1.DocumentText.Length > 0 Then
      My.Computer.Clipboard.SetText(WebBrowser1.DocumentText.Replace("<html><body>", "").Replace("</body></html>", ""))
    End If

  End Sub

#End Region

#Region "Open XML"
    ' Given a workbook file, a sheet name, and row and column integer values, retrieve the value of the cell.
    ' Call the function like this to retrieve the value from cell B3:
    ' Dim value As String = XLGetCellValueRowCol("Sample.xlsx", "Sheet1", 2, 3)
    Public Function XLGetCellValueRowCol(ByVal fileName As String, ByVal sheetName As String, ByVal colNumber As Integer, ByVal row As Integer) As String
        ' The challenge: Convert a cell number into a letter name.
        Return XLGetCellValueRowCol(fileName, sheetName, GetColumnName(colNumber), row)
    End Function

    Private Function GetColumnName(ByVal column As Integer) As String
        ' This algorithm was found here:
        ' http://stackoverflow.com/questions/181596/how-to-convert-a-column-number-eg-127-into-an-excel-column-eg-aa

        ' Given a column number, retrieve the corresponding
        ' column string name:
        Dim value As Integer
        Dim remainder As Integer
        Dim result As String = String.Empty
        value = column

        Do While (value > 0)
            remainder = (value - 1) Mod 26
            result = Chr(65 + remainder) & result
            value = CInt(Int((value - remainder) / 26))
        Loop
        Return result
    End Function

    ' Given a workbook file, a sheet name, and row and column names, retrieve the value of the cell.
    ' Call the function like this:
    ' Dim value As String = XLGetCellValueRowCol("Sample.xlsx", "Sheet1", "B", 3)
  Public Function XLGetCellValueRowCol(ByVal document As SpreadsheetDocument, ByVal sheetName As String, ByVal colName As String, ByVal row As Integer) As String
    Dim value As String = Nothing


    Dim wbPart As WorkbookPart = document.WorkbookPart

    ' Find the sheet with the supplied name, and then use that Sheet object
    ' to retrieve a reference to the appropriate worksheet.
    Dim theSheet As Sheet = wbPart.Workbook.Descendants(Of Sheet)(). _
      Where(Function(s) s.Name = sheetName).FirstOrDefault()

    If theSheet Is Nothing Then
      Throw New ArgumentException("sheetName")
    End If

    ' Retrieve a reference to the worksheet part, and then use its Worksheet property to get 
    ' a reference to the cell whose address matches the address you've supplied:
    Dim wsPart As WorksheetPart = CType(wbPart.GetPartById(theSheet.Id), WorksheetPart)
    Dim theCell As Cell = wsPart.Worksheet.Descendants(Of Cell). _
      Where(Function(c) c.CellReference = colName & row.ToString()).FirstOrDefault

    ' If the cell doesn't exist, return an empty string.
    If theCell IsNot Nothing Then
      value = theCell.InnerText

      ' If the cell represents an integer number, you're done. 
      ' For dates, this code returns the serialized value that 
      ' represents the date. The code handles strings and booleans
      ' individually. For shared strings, the code looks up the corresponding
      ' value in the shared string table. For booleans, the code converts 
      ' the value into the words TRUE or FALSE.
      If theCell.DataType IsNot Nothing Then
        Select Case theCell.DataType.Value
          Case CellValues.SharedString
            ' For shared strings, look up the value in the shared strings table.
            Dim stringTable = wbPart.GetPartsOfType(Of SharedStringTablePart).FirstOrDefault()
            ' If the shared string table is missing, something's wrong.
            ' Just return the index that you found in the cell.
            ' Otherwise, look up the correct text in the table.
            If stringTable IsNot Nothing Then
              value = stringTable.SharedStringTable.ElementAt(Integer.Parse(value)).InnerText
            End If
          Case CellValues.Boolean
            Select Case value
              Case "0"
                value = "FALSE"
              Case Else
                value = "TRUE"
            End Select
        End Select
      End If
    End If
    Return value
  End Function

#End Region

  Private Sub BuildRSSFiles(ByVal CatalogURL As String, ByVal savePath As String, ByVal saveFileName As String, ByVal enclosuretype As String)
    Dim ls = BuildListOpenXML(CatalogURL)

    Dim feed As SyndicationFeed = New SyndicationFeed("Greg's PDC09 Cast List -" & enclosuretype,
                                                      "PDC09",
                                                      New Uri("http://microsoftpdc.com/Videos"),
                                                      "PDC09" & enclosuretype,
                                                      DateTime.Now)

    feed.ImageUrl = New Uri("http://microsoftpdc.com/content/images/creative/PDC09Bling_General_ThreadsConnected_136.jpg")

    Dim items As List(Of SyndicationItem) = New List(Of SyndicationItem)()

    Const URL_Prefix As String = "http://ecn.channel9.msdn.com/o9/pdc09/"

    For Each s In ls
      Dim resoureURL As String = "" ' GetResourceURL(URL_Prefix & "wmv/", s.Code, "wmv", "WMV", True)
      Dim mimetype As String = ""

      Select Case enclosuretype.ToUpper
        Case "WMVHIGH"
          resoureURL = GetResourceURL(URL_Prefix & "wmvhigh/", s.Code, "wmv", "WMVHIGH", True)
          mimetype = "video/x-ms-wmv"
        Case "WMV"
          resoureURL = GetResourceURL(URL_Prefix & "wmv/", s.Code, "wmv", "WMV", True)
          mimetype = "video/x-ms-wmv"
        Case "MP4"
          resoureURL = GetResourceURL(URL_Prefix & "mp4/", s.Code, "mp4", "MP4", True)
          mimetype = "video/mp4"
        Case Else
          resoureURL = GetResourceURL(URL_Prefix & "wmv/", s.Code, "wmv", "WMV", True)
          mimetype = "video/x-ms-wmv"
      End Select
      
      If resoureURL.Length > 0 Then

        Dim item As SyndicationItem = New SyndicationItem(s.Code & " " & s.Title, s.Description, New Uri("http://microsoftpdc.com/Sessions/" & s.Code), s.Code, s.SessionDTTM)
        item.PublishDate = s.SessionDTTM

        item.ElementExtensions.Add(<enclosure url=<%= resoureURL %> length="0" type=<%= mimetype %>/>)

        items.Add(item)

      End If

    Next

    feed.Items = items

    Dim rssWriter As XmlWriter = XmlWriter.Create(IO.Path.Combine(savePath, saveFileName))
    Dim rssFormatter As Rss20FeedFormatter = New Rss20FeedFormatter(feed)
    rssFormatter.WriteTo(rssWriter)
    rssWriter.Close()
  End Sub

  Private Sub EnableUI(ByVal enable As Boolean)
    CatalogTextBox.Enabled = enable

    BuildListButton.Enabled = enable

    VerifyLinkCheckBox.Enabled = enable
    WMVCheckBox.Enabled = enable
    WMVHQCheckBox.Enabled = enable
    ZuneCheckBox.Enabled = enable
    MP4CheckBox.Enabled = enable
    ZIPCheckBox.Enabled = enable
    PPTXCheckBox.Enabled = enable
    CopyLinkLabel.Enabled = enable
    TabControl1.Enabled = enable

  End Sub

  Private Function BuildListOpenXML(ByVal CatalogURL As String) As Generic.List(Of Session)

    ' Open the document as read-only.
    Dim document As SpreadsheetDocument = SpreadsheetDocument.Open(CatalogURL, False)
    Dim ls = New Generic.List(Of Session)

    Using (document)
      Dim sheets As IEnumerable(Of Sheet) = document.WorkbookPart.Workbook.Descendants(Of Sheet)()
      If (sheets.Count() = 0) Then
        ' The specified worksheet does not exist.
        Return Nothing
      End If

      Dim worksheetPart As WorksheetPart = CType(document.WorkbookPart.GetPartById(sheets.First.Id), WorksheetPart)

      ' Get the cells in the specified column and order them by row.
      Dim rows As IEnumerable(Of Row) = worksheetPart.Worksheet.Descendants(Of Row)()

      If (rows.Count() = 0) Then
        ' The specified column does not exist.
        Return Nothing
      End If
      For Each r As Row In rows
        If CInt(r.RowIndex.Value.ToString) > 2 Then
          Dim title = XLGetCellValueRowCol(document, "MasterSessionGrid_091115_105718", "B", CInt(r.RowIndex.Value.ToString))
          Dim code = XLGetCellValueRowCol(document, "MasterSessionGrid_091115_105718", "A", CInt(r.RowIndex.Value.ToString))
          Dim description = XLGetCellValueRowCol(document, "MasterSessionGrid_091115_105718", "C", CInt(r.RowIndex.Value.ToString))
          Dim sessiondate = Date.FromOADate(XLGetCellValueRowCol(document, "MasterSessionGrid_091115_105718", "E", CInt(r.RowIndex.Value.ToString))).ToString("MM/dd/yyyy")

          Dim sessiontime = Date.FromOADate(XLGetCellValueRowCol(document, "MasterSessionGrid_091115_105718", "F", CInt(r.RowIndex.Value.ToString))).ToString("t")
          Dim alternateCode = XLGetCellValueRowCol(document, "MasterSessionGrid_091115_105718", "G", CInt(r.RowIndex.Value.ToString))
          Dim alternateSessionCode = XLGetCellValueRowCol(document, "MasterSessionGrid_091115_105718", "H", CInt(r.RowIndex.Value.ToString))


          If String.IsNullOrEmpty(alternateCode) Then
            alternateCode = ""
          End If

          If String.IsNullOrEmpty(alternateSessionCode) Then
            alternateSessionCode = ""
          End If

          If Not code.StartsWith("BOF") AndAlso code.Length > 0 AndAlso title.Length > 0 Then
            ls.Add(New Session() With {.Code = code, .Title = title,
                                       .Description = description, .SessionDTTM = sessiondate & " " & sessiontime,
                                       .AlternateCode = alternateCode, .AlternateSessionCode = alternateSessionCode})
          End If

        End If

      Next
    End Using

    Return ls

  End Function


  Private Function BuildHTML(ByVal ls As Generic.List(Of Session)) As String
    Dim q = From link In ls Order By link.SortTitle
    Dim sb = New System.Text.StringBuilder
    sb.Append("<html><body>")
    sb.Append("<table>")
    sb.Append(GetHeader())

    For Each link As Session In q
      sb.Append("<tr>")

      CurrentSessionLabel.Text = link.Code
      CurrentSessionLabel.Refresh()

      If Not link.Code.Contains("-R") Then
        sb.Append("<td>")
        sb.Append(GetTitle(link.SortTitle, link.Code, link.AlternateSessionCode, VerifyLinkCheckBox.Checked))

        sb.Append("</td>")
        sb.AppendLine(GetVideoLinks(link.Code, link.AlternateCode, VerifyLinkCheckBox.Checked))
      End If

      sb.Append("</tr>")

    Next
    sb.Append("</table>")

    sb.Append("</body></html>")


    Return sb.ToString
  End Function

  Private Function PageExists(ByVal URL As String, ByVal verifyLink As Boolean) As Boolean

    Return PageVerify.PageExists(URL, verifyLink)

  End Function

  Private Function GetTitle(ByVal title As String, ByVal code As String, ByVal alternateSessionCode As String, ByVal verifyLink As Boolean) As String
    'http://microsoftpdc.com/Sessions/CL09
    Dim sb As New System.Text.StringBuilder

    Dim workingcode = code
    If alternateSessionCode.Length > 0 Then
      workingcode = alternateSessionCode
    End If

    Dim url = "http://microsoftpdc.com/Sessions/" & workingcode

    If PageExists(url, False) Then
      sb.Append("<a href=")
      sb.Append(Chr(34))
      sb.Append(url)
      sb.Append(Chr(34))
      sb.Append(">")
      sb.Append(title)
      sb.Append("</a>")
    Else
      sb.Append(title)
    End If

    Return sb.ToString

  End Function

  Private Function GetVideoLinks(ByVal code As String, ByVal alternatecode As String, ByVal verifyLink As Boolean) As String
    'http://ecn.channel9.msdn.com/o9/pdc09/wmv/CL09.wmv
    'http://ecn.channel9.msdn.com/o9/pdc09/wmvhigh/CL09.wmv
    'http://ecn.channel9.msdn.com/o9/pdc09/ppt/CL09.pptx

    Const URL_Prefix As String = "http://ecn.channel9.msdn.com/o9/pdc09/"
    Dim sb = New System.Text.StringBuilder

    Dim workingcode = code

    If alternatecode.Length > 0 Then
      workingcode = alternatecode

    End If

    If WMVHQCheckBox.Checked Then
      sb.AppendLine(GetResourceLink(URL_Prefix & "wmvhigh/", workingcode, "wmv", "WMVHIGH", verifyLink))
    End If
    If WMVCheckBox.Checked Then
      sb.AppendLine(GetResourceLink(URL_Prefix & "wmv/", workingcode, "wmv", "WMV", verifyLink))
    End If
    If ZuneCheckBox.Checked Then
      sb.AppendLine(GetResourceLink(URL_Prefix & "zune/", workingcode, "wmv", "Zune", verifyLink))
    End If
    If MP4CheckBox.Checked Then
      sb.AppendLine(GetResourceLink(URL_Prefix & "mp4/", workingcode, "mp4", "MP4", verifyLink))
    End If
    If ZIPCheckBox.Checked Then
      sb.AppendLine(GetResourceLink(URL_Prefix & "zip/", code, "zip", "ZIP", verifyLink))
    End If
    If PPTXCheckBox.Checked Then
      sb.AppendLine(GetResourceLink(URL_Prefix & "ppt/", code, "pptx", "PPTX", verifyLink))
    End If

    Return sb.ToString
  End Function

  Private Function GetHeader() As String
    Dim sb = New System.Text.StringBuilder

    sb.Append("<thead>")
    sb.Append("<tr>")
    sb.Append("<th width=0>")
    sb.Append("Title")
    sb.Append("</th>")
    If WMVHQCheckBox.Checked Then
      sb.Append("<th width=75>")
      sb.Append("WMVHigh")
      sb.Append("</th>")
    End If
    If WMVCheckBox.Checked Then
      sb.Append("<th>")
      sb.Append("WMV")
      sb.Append("</th>")
    End If
    If ZuneCheckBox.Checked Then
      sb.Append("<th>")
      sb.Append("Zune")
      sb.Append("</th>")
    End If
    If MP4CheckBox.Checked Then
      sb.Append("<th>")
      sb.Append("MP4")
      sb.Append("</th>")
    End If
    If ZIPCheckBox.Checked Then
      sb.Append("<th>")
      sb.Append("ZIP")
      sb.Append("</th>")
    End If
    If PPTXCheckBox.Checked Then
      sb.Append("<th>")
      sb.Append("PPTX")
      sb.Append("</th>")
    End If


    sb.Append("</tr>")
    sb.Append("</thead>")

    Return sb.ToString

  End Function
  Private Function GetResourceURL(ByVal URLPrefix As String, ByVal code As String, ByVal extension As String, ByVal name As String, ByVal verifyLink As Boolean) As String
    Dim url = URLPrefix & code & "." & extension
    Dim url2 = URLPrefix & code.ToLower & "." & extension.ToLower
    Dim finalurl As String = ""

    Dim addlink As Boolean

    If PageExists(url, verifyLink) Then
      addlink = True
      finalurl = url
    Else
      addlink = False
    End If

    If addlink = False AndAlso PageExists(url2.ToLower, verifyLink) Then
      addlink = True
      finalurl = url2
    End If

    If addlink = False Then
      finalurl = ""
    End If

    Return finalurl

  End Function
  Private Function GetResourceLink(ByVal URLPrefix As String, ByVal code As String, ByVal extension As String, ByVal name As String, ByVal verifyLink As Boolean) As String
    Dim sb = New System.Text.StringBuilder
    Dim finalurl As String = GetResourceURL(URLPrefix, code, extension, name, verifyLink)
    Dim addlink As Boolean

    If finalurl.Length > 0 Then
      addlink = True
    Else
      addlink = False
    End If
    
    sb.Append("<td>")

    If addlink Then
      sb.Append("<a href=")
      sb.Append(Chr(34))
      sb.Append(finalurl)
      sb.Append(Chr(34))
      sb.Append(">")
      sb.Append(name)
      sb.Append("</a>")

    Else
      sb.Append("&nbsp")
    End If
    sb.Append("</td>")

    Return sb.ToString


  End Function


End Class